<template>
  <div class="container" :class="{ 'dark': isDark }">
    <el-header class="header">
      <div class="header-content">
        <h2>SQL语句生成器</h2>
        <div class="user-info">
          <ThemeToggle />
          <span>{{ userStore.userInfo?.username }}</span>
          <el-button type="danger" link @click="handleLogout">退出登录</el-button>
        </div>
      </div>
    </el-header>

    <main class="main-content">
      <div class="content-wrapper">
        <div class="input-section">
          <!-- 数据库连接选择器 -->
          <div class="db-connection-selector">
            <el-select 
              v-model="selectedConnectionId" 
              placeholder="选择数据库连接" 
              style="width: 240px"
              :loading="isLoadingConnections"
            >
              <el-option label="默认系统连接" :value="null" />
              <el-option
                v-for="conn in dbConnections"
                :key="conn.id"
                :label="conn.name + (conn.isDefault ? ' (默认)' : '')"
                :value="conn.id"
              />
            </el-select>
            <el-button type="primary" plain @click="openConnectionManager">
              管理数据库连接
            </el-button>
          </div>
          
          <el-tabs v-model="activeTab" class="main-tabs">
            <el-tab-pane label="SQL生成" name="generator">
              <h3>输入查询需求</h3>
              <el-input
                v-model="inputText"
                type="textarea"
                :rows="6"
                placeholder="请输入你的查询需求，例如：'查询所有年龄大于20岁的学生信息'"
              />
              
              <div class="button-container">
                <el-button 
                  type="primary" 
                  @click="generateSQL"
                  :loading="isLoading"
                  :disabled="!inputText.trim()"
                >
                  {{ isLoading ? '生成中...' : '生成SQL' }}
                </el-button>
              </div>

              <div v-if="sqlResult" class="output-section">
                <h3>生成的SQL语句</h3>
                <div class="sql-output">
                  <pre>{{ sqlResult }}</pre>
                  <div class="sql-actions">
                    <el-button 
                      class="action-btn"
                      size="small"
                      @click="copySQL"
                    >
                      <el-icon><Document /></el-icon>
                      复制
                    </el-button>
                    <el-button 
                      class="action-btn"
                      type="primary"
                      size="small"
                      @click="executeSQL"
                      :loading="isExecuting"
                    >
                      <el-icon><CaretRight /></el-icon>
                      执行SQL
                    </el-button>
                  </div>
                </div>
                
                <!-- 查询结果展示 -->
                <div v-if="queryResult" class="result-section">
                  <div class="result-header">
                    <h3>查询结果 ({{ queryResult.rowCount }} 行)</h3>
                    
                    <!-- 添加导出按钮 -->
                    <div class="export-actions">
                      <el-dropdown @command="handleExport" :disabled="!queryResult || !queryResult.data.length">
                        <el-button type="primary" size="small">
                          导出数据
                          <el-icon class="el-icon--right"><ArrowDown /></el-icon>
                        </el-button>
                        <template #dropdown>
                          <el-dropdown-menu>
                            <el-dropdown-item command="csv">导出CSV</el-dropdown-item>
                            <el-dropdown-item command="excel">导出Excel</el-dropdown-item>
                          </el-dropdown-menu>
                        </template>
                      </el-dropdown>
                    </div>
                    
                    <div class="view-toggle">
                      <el-radio-group v-model="activeView" size="small">
                        <el-radio-button label="table">表格</el-radio-button>
                        <el-radio-button label="chart">图表</el-radio-button>
                      </el-radio-group>
                    </div>
                  </div>
                  
                  <!-- 表格视图 -->
                  <div v-if="activeView === 'table'" class="table-view">
                    <el-table
                      :data="queryResult.data"
                      border
                      style="width: 100%"
                      max-height="400"
                    >
                      <el-table-column
                        v-for="col in queryResult.columns"
                        :key="col"
                        :prop="col"
                        :label="col"
                        show-overflow-tooltip
                      />
                    </el-table>
                  </div>
                  
                  <!-- 图表视图 -->
                  <div v-else-if="activeView === 'chart'" class="chart-view">
                    <query-chart
                      v-if="queryResult.data.length"
                      :data="queryResult.data"
                      :columns="queryResult.columns"
                    />
                    <el-empty v-else description="数据不适合图表展示" />
                  </div>
                </div>
              </div>
            </el-tab-pane>
            
            <el-tab-pane label="执行历史" name="executions">
              <div class="executions-panel">
                <h3>SQL执行记录</h3>
                <el-table
                  :data="executions"
                  border
                  style="width: 100%"
                  v-loading="isLoadingExecutions"
                >
                  <el-table-column label="执行时间" width="180">
                    <template #default="scope">
                      {{ formatDate(scope.row.executedAt) }}
                    </template>
                  </el-table-column>
                  <el-table-column label="SQL语句" show-overflow-tooltip>
                    <template #default="scope">
                      <el-tooltip 
                        effect="dark" 
                        :content="scope.row.sqlText" 
                        placement="top"
                        max-width="400"
                      >
                        <div class="sql-text-preview">{{ scope.row.sqlText }}</div>
                      </el-tooltip>
                    </template>
                  </el-table-column>
                  <el-table-column label="状态" width="100">
                    <template #default="scope">
                      <el-tag 
                        :type="scope.row.isSuccess ? 'success' : 'danger'"
                        size="small"
                      >
                        {{ scope.row.isSuccess ? '成功' : '失败' }}
                      </el-tag>
                    </template>
                  </el-table-column>
                  <el-table-column prop="rowCount" label="返回行数" width="100" />
                  <el-table-column label="执行时间" width="120">
                    <template #default="scope">
                      {{ formatExecutionTime(scope.row.executionTimeMs) }}
                    </template>
                  </el-table-column>
                  <el-table-column label="数据库连接" width="150">
                    <template #default="scope">
                      {{ scope.row.connectionName || '默认连接' }}
                    </template>
                  </el-table-column>
                  <el-table-column label="操作" width="120">
                    <template #default="scope">
                      <el-button 
                        type="primary" 
                        size="small" 
                        plain
                        @click="reuseSQL(scope.row.sqlText)"
                      >
                        重用SQL
                      </el-button>
                    </template>
                  </el-table-column>
                </el-table>
              </div>
            </el-tab-pane>
          </el-tabs>
        </div>

        <div class="history-section">
          <div class="history-panel">
            <h3>历史记录</h3>
            <div v-loading="isLoadingHistory">
              <div class="history-list">
                <el-card
                  v-for="(item, index) in history"
                  :key="index"
                  class="history-item"
                  @click="fillFromHistory(item)"
                >
                  <template #header>
                    <div class="history-header">
                      <span class="query-text">{{ truncateText(item.inputText) }}</span>
                    </div>
                  </template>
                  <pre class="sql-preview">{{ item.sqlResult }}</pre>
                </el-card>
                
                <!-- 没有历史记录时显示提示 -->
                <el-empty 
                  v-if="!history.length && !isLoadingHistory" 
                  description="暂无历史记录" 
                />
              </div>
              
              <!-- 分页控件 -->
              <div class="pagination-container" v-if="historyPagination.total > 0">
                <el-pagination
                  background
                  layout="prev, pager, next, sizes"
                  :total="historyPagination.total"
                  :page-size="historyPagination.pageSize"
                  :current-page="historyPagination.page"
                  :page-sizes="[5, 10, 20, 50]"
                  @size-change="handleHistorySizeChange"
                  @current-change="handleHistoryPageChange"
                />
              </div>
            </div>
          </div>
        </div>
      </div>
    </main>
    
    <!-- 数据库连接管理器 -->
    <el-dialog 
      v-model="showConnectionManager"
      title="数据库连接管理"
      width="80%"
      @closed="handleConnectionManagerClose"
    >
      <db-connection-manager />
    </el-dialog>
  </div>
</template>

<script setup>
import { ref, onMounted } from 'vue'
import { useRouter } from 'vue-router'
import { useUserStore } from '../stores/user'
import { ElMessage } from 'element-plus'
import { translateToSql, getHistory, executeSql, getExecutions } from '../api/translate'
import ThemeToggle from './ThemeToggle.vue'
import { Document, CaretRight, ArrowDown } from '@element-plus/icons-vue'
import QueryChart from './QueryChart.vue'
import DbConnectionManager from './DbConnectionManager.vue'
import { 
  translateToSqlWithConnection, 
  executeSqlWithConnection 
} from '../api/translate'
import { 
  getUserConnections, 
  getDefaultConnection 
} from '../api/dbConnection'

const router = useRouter()
const userStore = useUserStore()

const inputText = ref('')
const sqlResult = ref('')
const isLoading = ref(false)
const history = ref([])
const isDark = ref(false)
const isExecuting = ref(false)
const queryResult = ref(null)
const activeView = ref('table')
const activeTab = ref('generator')
const executions = ref([])
const isLoadingExecutions = ref(false)

// 数据库连接管理相关
const dbConnections = ref([])
const selectedConnectionId = ref(null)
const defaultConnectionId = ref(null)
const isLoadingConnections = ref(false)
const showConnectionManager = ref(false)

// 添加历史记录分页相关变量
const historyPagination = ref({
  page: 1,
  pageSize: 10,
  total: 0
})
const isLoadingHistory = ref(false)

// 加载历史记录和执行记录
onMounted(async () => {
  await loadHistory()
  await loadConnections()
  await loadExecutions()
  isDark.value = localStorage.getItem('theme') === 'dark'
})

// 生成SQL
const generateSQL = async () => {
  if (!inputText.value.trim()) return
  
  isLoading.value = true
  sqlResult.value = ''
  
  try {
    let response
    
    // 根据是否选择了数据库连接决定调用哪个API
    if (selectedConnectionId.value) {
      response = await translateToSqlWithConnection(inputText.value, selectedConnectionId.value)
    } else {
      response = await translateToSql(inputText.value)
    }
    
    sqlResult.value = response.sql
    await loadHistory()
  } catch (error) {
    ElMessage.error(error.message || '生成失败')
  } finally {
    isLoading.value = false
  }
}

// 加载历史记录
async function loadHistory() {
  isLoadingHistory.value = true
  try {
    const response = await getHistory(historyPagination.value.page, historyPagination.value.pageSize)
    history.value = response.records || []
    historyPagination.value.total = response.total || 0
    historyPagination.value.page = response.page || 1
    historyPagination.value.pageSize = response.pageSize || 10
  } catch (error) {
    ElMessage.error('加载历史记录失败')
  } finally {
    isLoadingHistory.value = false
  }
}

// 从历史记录填充
function fillFromHistory(item) {
  inputText.value = item.inputText
  sqlResult.value = item.sqlResult
}

// 复制SQL
async function copySQL() {
  try {
    await navigator.clipboard.writeText(sqlResult.value)
    ElMessage.success('复制成功')
  } catch (err) {
    ElMessage.error('复制失败')
  }
}

// 截断文本
function truncateText(text) {
  return text.length > 50 ? text.slice(0, 50) + '...' : text
}

// 执行SQL
const executeSQL = async () => {
  if (!sqlResult.value) return
  
  isExecuting.value = true
  queryResult.value = null
  
  try {
    let result
    
    // 根据是否选择了数据库连接决定调用哪个API
    if (selectedConnectionId.value) {
      result = await executeSqlWithConnection(sqlResult.value, selectedConnectionId.value)
    } else {
      result = await executeSql(sqlResult.value)
    }
    
    queryResult.value = result
  } catch (error) {
    ElMessage.error(error.message || 'SQL执行失败')
  } finally {
    isExecuting.value = false
  }
}

// 退出登录
const handleLogout = async () => {
  await userStore.logout()
  ElMessage.success('已成功退出登录')
  router.push('/login')
}

// 加载数据库连接列表
const loadConnections = async () => {
  isLoadingConnections.value = true
  try {
    dbConnections.value = await getUserConnections()
    
    // 获取默认连接
    try {
      const defaultConn = await getDefaultConnection()
      if (defaultConn && defaultConn.id) {
        defaultConnectionId.value = defaultConn.id
        selectedConnectionId.value = defaultConn.id
      }
    } catch (error) {
      console.error('获取默认连接失败:', error)
    }
  } catch (error) {
    ElMessage.error('加载数据库连接失败')
  } finally {
    isLoadingConnections.value = false
  }
}

// 打开连接管理器
const openConnectionManager = () => {
  showConnectionManager.value = true
}

// 关闭连接管理器并刷新连接列表
const handleConnectionManagerClose = async () => {
  showConnectionManager.value = false
  await loadConnections()
}

// 加载执行记录
async function loadExecutions() {
  isLoadingExecutions.value = true
  try {
    executions.value = await getExecutions()
  } catch (error) {
    ElMessage.error('加载SQL执行记录失败')
  } finally {
    isLoadingExecutions.value = false
  }
}

// 格式化执行时间
function formatExecutionTime(ms) {
  if (!ms) return '-'
  
  if (ms < 1000) {
    return `${ms}ms`
  } else {
    return `${(ms / 1000).toFixed(2)}s`
  }
}

// 重用SQL
function reuseSQL(sql) {
  sqlResult.value = sql
  activeTab.value = 'generator'
}

// 格式化日期
function formatDate(dateString) {
  if (!dateString) return '-'
  
  const date = new Date(dateString)
  return date.toLocaleString()
}

// 导出数据
const handleExport = (command) => {
  if (!queryResult.value || !queryResult.value.data.length) return;
  
  try {
    if (command === 'csv') {
      exportToCsv(queryResult.value.data, queryResult.value.columns);
    } else if (command === 'excel') {
      exportToExcel(queryResult.value.data, queryResult.value.columns);
    }
  } catch (error) {
    ElMessage.error('导出失败: ' + error.message);
  }
}

// 导出为CSV
const exportToCsv = (data, columns) => {
  // 构建CSV内容
  const csvContent = [
    columns.join(','), // 表头
    ...data.map(row => 
      columns.map(col => {
        const value = row[col];
        // 如果是字符串且包含逗号或引号，需要用引号包裹并转义引号
        if (typeof value === 'string' && (value.includes(',') || value.includes('"'))) {
          return `"${value.replace(/"/g, '""')}"`;
        }
        // 如果是null或undefined，返回空字符串
        if (value === null || value === undefined) {
          return '';
        }
        return value;
      }).join(',')
    )
  ].join('\n');
  
  // 添加BOM以确保Excel正确识别UTF-8编码
  const bom = new Uint8Array([0xEF, 0xBB, 0xBF]);
  const blob = new Blob([bom, csvContent], { type: 'text/csv;charset=utf-8;' });
  
  // 创建下载链接并触发下载
  downloadFile(blob, `sql_query_result_${new Date().getTime()}.csv`);
  
  ElMessage.success('CSV导出成功');
}

// 导出为Excel (通过HTML表格方式)
const exportToExcel = (data, columns) => {
  // 创建一个HTML表格字符串，该方法更可靠地被Excel识别
  let tableHtml = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">';
  
  // 添加Excel特定的元信息
  tableHtml += '<head>';
  tableHtml += '<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>Sheet1</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->';
  tableHtml += '<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>';
  tableHtml += '</head>';
  
  tableHtml += '<body>';
  tableHtml += '<table>';
  
  // 添加表头
  tableHtml += '<tr>';
  columns.forEach(col => {
    tableHtml += `<th style="font-weight: bold; background-color: #f2f2f2; border: 1px solid #cccccc;">${escapeHtml(String(col))}</th>`;
  });
  tableHtml += '</tr>';
  
  // 添加数据行
  data.forEach(row => {
    tableHtml += '<tr>';
    columns.forEach(col => {
      const value = row[col];
      let cellValue = '';
      
      if (value === null || value === undefined) {
        cellValue = '';
      } else if (value instanceof Date) {
        cellValue = value.toLocaleString();
      } else {
        cellValue = String(value);
      }
      
      tableHtml += `<td style="border: 1px solid #cccccc;">${escapeHtml(cellValue)}</td>`;
    });
    tableHtml += '</tr>';
  });
  
  tableHtml += '</table>';
  tableHtml += '</body>';
  tableHtml += '</html>';
  
  // 创建Blob对象，使用HTML的MIME类型
  const blob = new Blob([tableHtml], { type: 'application/vnd.ms-excel;charset=utf-8' });
  
  // 下载文件
  downloadFile(blob, `sql_query_result_${new Date().getTime()}.xls`);
  
  ElMessage.success('Excel导出成功');
}

// HTML转义函数，防止特殊字符破坏HTML结构
const escapeHtml = (text) => {
  return text
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#039;');
}

// 通用文件下载功能
const downloadFile = (blob, filename) => {
  const url = URL.createObjectURL(blob);
  const link = document.createElement('a');
  link.href = url;
  link.download = filename;
  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
  URL.revokeObjectURL(url); // 释放URL对象
}

// 切换历史记录页码
function handleHistoryPageChange(page) {
  historyPagination.value.page = page
  loadHistory()
}

// 切换每页显示数量
function handleHistorySizeChange(size) {
  historyPagination.value.pageSize = size
  historyPagination.value.page = 1
  loadHistory()
}
</script>

<style>
/* 全局深色模式样式 */
:root {
  --bg-color: #f5f7fa;
  --card-bg: #ffffff;
  --text-color: #303133;
  --border-color: #e4e7ed;
  --code-bg: #2d3436;
  --code-color: #dfe6e9;
}

:root.dark {
  --bg-color: #1a1a1a;
  --card-bg: #2c2c2c;
  --text-color: #e0e0e0;
  --border-color: #404040;
  --code-bg: #1e1e1e;
  --code-color: #e0e0e0;
}

.pagination-container {
  margin-top: 16px;
  text-align: center;
}
</style>

<style scoped>
.container {
  min-height: 100vh;
  background-color: var(--bg-color);
  color: var(--text-color);
}

.header {
  background-color: var(--card-bg);
  box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
  position: fixed;
  top: 0;
  left: 0;
  right: 0;
  z-index: 100;
}

.header-content {
  max-width: 1400px;
  margin: 0 auto;
  height: 60px;
  display: flex;
  justify-content: space-between;
  align-items: center;
  padding: 0 20px;
}

.user-info {
  display: flex;
  align-items: center;
  gap: 12px;
}

.content-wrapper {
  display: flex;
  gap: 24px;
  margin-top: 80px;
  padding: 24px;
  max-width: 1400px;
  margin: 80px auto 0;
}

.input-section {
  flex: 2;
  background: var(--card-bg);
  border-radius: 8px;
  padding: 24px;
  box-shadow: 0 2px 12px 0 rgba(0,0,0,0.1);
  border: 1px solid var(--border-color);
}

/* 数据库连接选择器样式 */
.db-connection-selector {
  display: flex;
  align-items: center;
  gap: 12px;
  margin-bottom: 20px;
  padding-bottom: 16px;
  border-bottom: 1px dashed var(--border-color);
}

.history-section {
  flex: 1;
  min-width: 300px;
}

.history-panel {
  background: var(--card-bg);
  border-radius: 8px;
  padding: 24px;
  box-shadow: 0 2px 12px 0 rgba(0,0,0,0.1);
  border: 1px solid var(--border-color);
}

.button-container {
  margin-top: 16px;
  display: flex;
  justify-content: flex-end;
}

.output-section {
  margin-top: 20px;
}

.sql-output {
  background: var(--code-bg);
  color: var(--code-color);
  padding: 1.5rem;
  padding-top: 3rem; /* 为按钮留出空间 */
  border-radius: 8px;
  position: relative;
  margin-top: 1rem;
  font-family: 'Fira Code', monospace;
}

.sql-actions {
  position: absolute;
  top: 8px;
  right: 8px;
  display: flex;
  gap: 8px;
}

.main-tabs {
  margin-top: 20px;
}

.executions-panel {
  background: var(--card-bg);
  border-radius: 8px;
  padding: 0;
}

.sql-text-preview {
  max-width: 300px;
  overflow: hidden;
  text-overflow: ellipsis;
  white-space: nowrap;
}

.action-btn {
  display: flex;
  align-items: center;
  gap: 4px;
}

.history-list {
  display: flex;
  flex-direction: column;
  gap: 12px;
}

.history-item {
  cursor: pointer;
  transition: transform 0.2s;
  margin-bottom: 12px;
  border-color: var(--border-color);
}

.history-item:last-child {
  margin-bottom: 0;
}

.history-item:hover {
  background-color: var(--bg-color);
}

.history-header {
  display: flex;
  align-items: center;
  justify-content: space-between;
}

.query-text {
  font-size: 14px;
  color: var(--text-color);
}

.sql-preview {
  font-size: 12px;
  color: var(--text-color);
  margin: 0;
  white-space: pre-wrap;
  max-height: 60px;
  overflow: hidden;
  opacity: 0.8;
}

h3 {
  margin-bottom: 16px;
  color: var(--text-color);
}

pre {
  margin: 0;
  white-space: pre-wrap;
}

.result-section {
  margin-top: 20px;
  background: var(--card-bg);
  border-radius: 8px;
  padding: 20px;
  border: 1px solid var(--border-color);
}

.result-header {
  margin-bottom: 20px;
}

.export-actions {
  margin-right: 12px;
}

.view-toggle {
  margin-top: 12px;
}

.table-view {
  margin-top: 20px;
}

.chart-view {
  margin-top: 20px;
}

@media (max-width: 1024px) {
  .content-wrapper {
    flex-direction: column;
  }
  
  .history-section {
    order: -1;
    margin-bottom: 24px;
  }
  
  .db-connection-selector {
    flex-direction: column;
    align-items: flex-start;
  }
  
  .db-connection-selector .el-select {
    width: 100% !important;
  }
  
  .container {
    padding: 16px;
  }
  
  .sql-card, .history-card {
    padding: 16px;
  }
  
  .actions {
    flex-direction: column;
    align-items: stretch;
  }
  
  .actions button {
    margin-bottom: 8px;
  }
}
</style>